package project

import (
	"context"
	"encoding/json"
	"fmt"
	"gf-assistant/internal/common/factory"
	"gf-assistant/internal/project/parameter"
	"github.com/gogf/gf/v2/util/gconv"
	"reflect"
	"strings"

	"github.com/gogf/gf/v2/database/gdb"
	"github.com/gogf/gf/v2/frame/g"
)

type ProjectDao struct{}

func init() {
	projectDao := ProjectDao{}
	factory.Put(reflect.TypeOf(projectDao).String(), &projectDao)
}

// DB retrieves and returns the underlying raw database management object of current DAO.
func (dao *ProjectDao) DB() gdb.DB {
	return g.DB()
}

func (dao *ProjectDao) GetByProjectCode(ctx context.Context, projectCode string) (res *parameter.GetProjectRes) {
	db := g.DB()
	sql := `
		  select t.PROJECT_ID as ProjectId,t.PROJECT_CODE as ProjectCode,t.PROJECT_NAME as ProjectName,t.PROJECT_URL as ProjectUrl,
		         t.LEADER_ID as LeaderId,l.NICK_NAME as LeaderName,t.REMARK as Remark,
		         t.ENABLE_STATUS as EnableStatus ,ELT(FIELD(t.ENABLE_STATUS, 0, 1),'无效','有效')AS EnableStatusName,
		         c.NICK_NAME as CreatedUserName,p.NICK_NAME as UpdatedUserName,
				 DATE_FORMAT(t.CREATE_TIME,'%Y-%m-%d %T') as CreatedTime,
				 DATE_FORMAT(t.UPDATE_TIME,'%Y-%m-%d %T') as UpdatedTime
		  from PROJECT_INFO t 
		  left join SYS_USER l on t.LEADER_ID=l.USER_ID
		  left join SYS_USER c on t.CREATE_USER=c.USER_ID
		  left join SYS_USER p on t.UPDATE_USER=p.USER_ID
		  where t.PROJECT_CODE = ?`
	one, err := db.GetOne(ctx, sql, projectCode)
	if err != nil || one == nil {
		return res
	}
	err = json.Unmarshal([]byte(one.Json()), &res)
	if err != nil {
		fmt.Println("JSON unmarshal error:", err)
	}
	return res
}

func (dao *ProjectDao) GetProject(ctx context.Context, req *parameter.GetProjectReq) (res *parameter.GetProjectRes) {

	db := g.DB()
	sql := `
		  select t.PROJECT_ID as ProjectId,t.PROJECT_CODE as ProjectCode,t.PROJECT_NAME as ProjectName,t.PROJECT_URL as ProjectUrl,
		         t.LEADER_ID as LeaderId,l.NICK_NAME as LeaderName,t.REMARK as Remark,
		         t.ENABLE_STATUS as EnableStatus ,ELT(FIELD(t.ENABLE_STATUS, 0, 1),'无效','有效')AS EnableStatusName,
		         c.NICK_NAME as CreatedUserName,p.NICK_NAME as UpdatedUserName,
				 DATE_FORMAT(t.CREATE_TIME,'%Y-%m-%d %T') as CreatedTime,
				 DATE_FORMAT(t.UPDATE_TIME,'%Y-%m-%d %T') as UpdatedTime
		  from PROJECT_INFO t 
		  left join SYS_USER l on t.LEADER_ID=l.USER_ID
		  left join SYS_USER c on t.CREATE_USER=c.USER_ID
		  left join SYS_USER p on t.UPDATE_USER=p.USER_ID
		  where t.PROJECT_ID = ?`
	one, err := db.GetOne(ctx, sql, req.ProjectId)
	if err != nil || one == nil {
		return res
	}
	err = json.Unmarshal([]byte(one.Json()), &res)
	if err != nil {
		fmt.Println("JSON unmarshal error:", err)
	}
	return res
}

func (dao *ProjectDao) ListProject(ctx context.Context, req *parameter.ListProjectReq) (projects []*parameter.GetProjectRes) {

	parameters := make([]interface{}, 0)
	sql := `
		  select t.PROJECT_ID as ProjectId,t.PROJECT_CODE as ProjectCode,t.PROJECT_NAME as ProjectName,t.PROJECT_URL as ProjectUrl,
		         t.LEADER_ID as LeaderId,l.NICK_NAME as LeaderName,t.REMARK as Remark,
		         t.ENABLE_STATUS as EnableStatus ,ELT(FIELD(t.ENABLE_STATUS, 0, 1),'无效','有效')AS EnableStatusName,
		         c.NICK_NAME as CreatedUserName,p.NICK_NAME as UpdatedUserName,
				 DATE_FORMAT(t.CREATE_TIME,'%Y-%m-%d %T') as CreatedTime,
				 DATE_FORMAT(t.UPDATE_TIME,'%Y-%m-%d %T') as UpdatedTime
		  from PROJECT_INFO t 
		  left join SYS_USER l on t.LEADER_ID=l.USER_ID
		  left join SYS_USER c on t.CREATE_USER=c.USER_ID
		  left join SYS_USER p on t.UPDATE_USER=p.USER_ID
		  where exists(select 1 from PROJECT_MEMBER m where m.PROJECT_ID=t.PROJECT_ID and m.USER_ID=?)
		  `
	parameters = append(parameters, req.GetUserId()) //添加参数
	if req.ProjectName != "" {
		sql = sql + " and t.PROJECT_NAME like ?\n"
		parameters = append(parameters, "%"+strings.TrimSpace(req.ProjectName)+"%") //项目名称
	}
	if req.ProjectCode != "" {
		sql = sql + " and t.PROJECT_CODE = ?\n"
		parameters = append(parameters, strings.TrimSpace(req.ProjectCode)) //项目编码
	}
	if req.LeaderId != 0 {
		sql = sql + " and t.LEADER_ID=?\n"
		parameters = append(parameters, req.LeaderId) //项目负责人
	}

	if req.EnableStatus > -1 {
		sql = sql + " and t.ENABLE_STATUS=?\n"
		parameters = append(parameters, req.EnableStatus) //项目状态
	}
	sql = sql + " order by t.ENABLE_STATUS desc, t.UPDATE_TIME desc \n"
	sql = sql + " limit ?,?"
	parameters = append(parameters, (req.Page-1)*req.Size) //开始下标
	parameters = append(parameters, req.Size)              //结束下标

	resultList, err := g.DB().GetAll(ctx, sql, parameters)
	if err != nil {
		fmt.Printf("query faied, error:[%v]", err.Error())
		return
	}
	//遍历结构体切片
	for _, v := range resultList {
		project := parameter.GetProjectRes{}
		err = json.Unmarshal([]byte(v.Json()), &project)
		if err != nil {
			fmt.Println("JSON unmarshal error:", err)
		}
		projects = append(projects, &project)
	}
	return projects
}

func (dao *ProjectDao) GetProjectTree(ctx context.Context, req *parameter.GetProjectTreeReq) (res []*parameter.ProjectTreeRes) {
	parameters := make([]interface{}, 0)
	sql := `
		  select t.PROJECT_ID as Id,0 as PId,concat(t.PROJECT_NAME,'(',t.PROJECT_CODE,')') as Title
		  from PROJECT_INFO t 
		  where exists(select 1 from PROJECT_MEMBER m where m.PROJECT_ID=t.PROJECT_ID and m.USER_ID=?)
		  `
	parameters = append(parameters, req.GetUserId())
	sql = sql + " order by t.ENABLE_STATUS desc, t.UPDATE_TIME desc \n"
	resultList, err := g.DB().GetAll(ctx, sql, parameters)
	if err != nil {
		fmt.Printf("query faied, error:[%v]", err.Error())
		return
	}
	//遍历结构体切片
	for _, v := range resultList {
		project := parameter.ProjectTreeRes{}
		err = json.Unmarshal([]byte(v.Json()), &project)
		if err != nil {
			fmt.Println("JSON unmarshal error:", err)
		}
		res = append(res, &project)
	}
	return res
}
func (dao *ProjectDao) GetProjectCombox(ctx context.Context, req *parameter.GetProjectComboxReq) (res []*parameter.ProjectComboxRes) {
	parameters := make([]interface{}, 0)
	sql := `
		  select t.PROJECT_ID as Value,concat(t.PROJECT_NAME,'(',t.PROJECT_CODE,')') as Name
		  from PROJECT_INFO t 
		  where t.ENABLE_STATUS=1
		  `
	if req.AllState == 0 {
		and := `
			and exists(
				select 1 
				from PROJECT_MEMBER m 
				where m.PROJECT_ID=t.PROJECT_ID
					and m.ENABLE_STATUS=1
					and m.USER_ID=?
			)
		`
		sql = sql + and
		parameters = append(parameters, req.GetUserId())
	}
	sql = sql + " order by t.PROJECT_NAME \n"
	resultList, err := g.DB().GetAll(ctx, sql, parameters)
	if err != nil {
		fmt.Printf("query faied, error:[%v]", err.Error())
		return
	}
	//遍历结构体切片
	for _, v := range resultList {
		project := parameter.ProjectComboxRes{}
		err = json.Unmarshal([]byte(v.Json()), &project)
		if err != nil {
			fmt.Println("JSON unmarshal error:", err)
		}
		res = append(res, &project)
	}
	return res
}
func (dao *ProjectDao) CountProject(ctx context.Context, req *parameter.ListProjectReq) (count int64) {
	parameters := make([]interface{}, 0)
	sql := `
		  select count(1) as projectCnt
		  from PROJECT_INFO t 
		  where exists(select 1 from PROJECT_MEMBER m where m.PROJECT_ID=t.PROJECT_ID and m.USER_ID=?)
		  `
	parameters = append(parameters, req.GetUserId()) //添加参数
	if req.ProjectName != "" {
		sql = sql + " and t.PROJECT_NAME like ?\n"
		parameters = append(parameters, "%"+strings.TrimSpace(req.ProjectName)+"%") //项目名称
	}
	if req.ProjectCode != "" {
		sql = sql + " and t.PROJECT_CODE = ?\n"
		parameters = append(parameters, strings.TrimSpace(req.ProjectCode)) //项目编码
	}
	if req.LeaderId != 0 {
		sql = sql + " and t.LEADER_ID=?\n"
		parameters = append(parameters, req.LeaderId) //项目负责人
	}

	if req.EnableStatus > -1 {
		sql = sql + " and t.ENABLE_STATUS=?\n"
		parameters = append(parameters, req.EnableStatus) //项目状态
	}

	one, err := g.DB().GetOne(ctx, sql, parameters)
	if err != nil {
		fmt.Printf("query faied, error:[%v]", err.Error())
		return
	}
	count = gconv.Int64(one.Map()["projectCnt"])
	return count
}
func (dao *ProjectDao) CreateProject(tx gdb.TX, req *parameter.CreateProjectReq) (res *parameter.CreateProjectRes, err error) {
	sql := `
			insert into PROJECT_INFO(PROJECT_CODE,PROJECT_NAME,REMARK,PROJECT_URL,ENABLE_STATUS,CREATE_TIME,CREATE_USER,UPDATE_TIME,UPDATE_USER)
			values (?,?,?,?,?,now(),?,now(),?)
		  `
	parameters := make([]interface{}, 0)
	parameters = append(parameters, req.ProjectCode, req.ProjectName, req.Remark, req.ProjectUrl, 1, req.GetUserId(), req.GetUserId())

	result, err := tx.Exec(sql, parameters)
	if err != nil {
		fmt.Printf("insert project faied, error:[%v]", err.Error())
		return nil, err
	}
	insertId, err := result.LastInsertId()
	return &parameter.CreateProjectRes{ProjectId: gconv.Uint(insertId)}, err
}
func (dao *ProjectDao) DeleteProject(tx gdb.TX, req *parameter.DeleteProjectReq) (res *parameter.DeleteProjectRes, err error) {
	sql := `
			update PROJECT_INFO set 
			   ENABLE_STATUS=0,
			   UPDATE_TIME=now(), 
			   UPDATE_USER=?
			where PROJECT_ID=?
		  `
	parameters := make([]interface{}, 0)
	parameters = append(parameters, req.GetUserId(), req.ProjectId)
	_, err = tx.Exec(sql, parameters)
	if err != nil {
		fmt.Printf("delete project faied, error:[%v]", err.Error())
		return nil, err
	}
	return &parameter.DeleteProjectRes{}, err
}
func (dao *ProjectDao) SaveProject(tx gdb.TX, req *parameter.SaveProjectReq) {
	sql := `
			update PROJECT_INFO set 
			   PROJECT_CODE=?,
			   PROJECT_NAME=?,
			   REMARK=?,
			   PROJECT_URL=?,
			   UPDATE_TIME=now(), 
			   UPDATE_USER=?
			where PROJECT_ID=?
		  `
	parameters := make([]interface{}, 0)
	parameters = append(parameters, req.ProjectCode, req.ProjectName, req.Remark, req.ProjectUrl, req.GetUserId(), req.ProjectId)
	_, _ = tx.Exec(sql, parameters)
}
